package com.bootdo.system.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.bootdo.system.domain.Course;
import com.bootdo.system.domain.CourseTime;
import com.bootdo.system.domain.SysUserDO;

public interface CourseMapper {


	@Select("select * from tb_course where id = #{id}")
	Course get(Long id);
	
	@Select("<script>" +
			"select c.*,ct.id subId,ct.startTime subTime,ct.coursePoint subPoint,"
			+ "ct.maxBabys subMaxBabys, ct.remainderNumber subRemainderNumber,"
			+ "ct.signedNumber subSignedNumber,ct.courseStatus subStatus,ct.courseDuration subDuration "
			+ " from tb_course c left join tb_course_time ct on c.id=ct.courseId " + 
			"<where>" + 
		  		  "<if test=\"id != null and id != ''\">"+ "and id = #{id} " + "</if>" + 
		  		  "<if test=\"bdId != null and bdId != ''\">"+ "and courseOwner in(select id from tb_customer where bdId = #{bdId}) " + "</if>" + 
		  		  "<if test=\"placeFirst != null and placeFirst != ''\">"+ "and placeFirst = #{placeFirst} " + "</if>" + 
		  		  "<if test=\"placeSecond != null and placeSecond != ''\">"+ "and placeSecond = #{placeSecond} " + "</if>" + 
		  		  "<if test=\"ageRange != null and ageRange != ''\">"+ "and ageRange = #{ageRange} " + "</if>" + 
		  		  "<if test=\"coursePoints != null and coursePoints != ''\">"+ "and coursePoints = #{coursePoints} " + "</if>" + 
		  		  "<if test=\"courseType != null and courseType != ''\">"+ "and courseType = #{courseType} " + "</if>" + 
		  		  "<if test=\"courseSpec != null and courseSpec != ''\">"+ "and courseSpec = #{courseSpec} " + "</if>" + 
		  		  "<if test=\"courseStatus != null and courseStatus != ''\">"+ "and courseStatus = #{courseStatus} " + "</if>" + 
		  		  "<if test=\"courseOwner != null and courseOwner != ''\">"+ "and courseOwner = #{courseOwner} " + "</if>" + 
		  			"</where>"+ 
			" <choose>" + 
	            "<when test=\"sort != null and sort.trim() != ''\">" + 
	                "order by ${sort} ${order}" + 
	            "</when>" + 
				"<otherwise>" + 
	                "order by id desc" + 
				"</otherwise>" + 
	        "</choose>"+
			"</script>")
	List<Map<String,Object>> list(Map<String,Object> map);
	
	
	@Select("<script>" +
			"select c.*,ct.id subId,ct.startTime subTime,ct.coursePoint subPoint,"
			+ "ct.maxBabys subMaxBabys, ct.remainderNumber subRemainderNumber,"
			+ "ct.signedNumber subSignedNumber,ct.courseStatus subStatus,ct.courseDuration subDuration "
			+ " from tb_course c left join tb_course_time ct on c.id=ct.courseId " + 
					"<where> " + 
						  " c.courseStatus=2 "+
				  		  "<if test=\"id != null and id != ''\">"+ " and c.id = #{id} " + "</if>" + 
				  		  "<if test=\"bdId != null and bdId != ''\">"+ " and c.courseOwner in(select id from tb_customer where bdId = #{bdId}) " + "</if>" +
  						  "<if test=\"courseTime != null and courseTime != ''\">"+ " and left(ct.startTime,10)=#{courseTime}" + "</if>" + 
  						  "<if test=\"startAge != null and startAge != ''\">"+ " and (((c.maxAge*12)&gt;=#{startAge} and (c.maxAge*12)&lt;#{endAge}) or((c.minAge*12)&gt;=#{startAge} and (c.minAge*12)&lt;#{endAge}) )" + "</if>" + 
  						  "<if test=\"startPoint != null and startPoint != ''\">"+ "and ct.coursePoint&gt;=#{startPoint}" + "</if>" + 
  						  "<if test=\"endPoint != null and endPoint != ''\">"+ " and ct.coursePoint&lt;=#{endPoint}" + "</if>" + 
  						  "<if test=\"placeFirst != null and placeFirst != ''\">"+ " and placeFirst in( #{placeFirst}) " + "</if>" + 
				  		  "<if test=\"placeSecond != null and placeSecond != ''\">"+ " and placeSecond in (#{placeSecond}) " + "</if>" + 
				  		  "<if test=\"courseType != null and courseType != ''\">"+ " and courseType = #{courseType} " + "</if>" + 
				  		  "<if test=\"courseTypeName != null and courseTypeName != ''\">"+ " and courseType in(select sd.id from sys_dict sd where sd.type='kctype' and sd.value= #{courseTypeName}) " + "</if>" + 
				  		  "<if test=\"courseSpec != null and courseSpec != ''\">"+ " and courseSpec = #{courseSpec} " + "</if>" + 
				  		  "<if test=\"courseStatus != null and courseStatus != ''\">"+ " and courseStatus = #{courseStatus} " + "</if>" + 
				  		  "<if test=\"courseOwner != null and courseOwner != ''\">"+ "and courseOwner = #{courseOwner} " + "</if>" + 
				  		  " and exists(select cu.id from tb_customer cu where cu.id=c.courseOwner and cu.status=2)" + 
				  		  "</where>"+ 
					" <choose>" + 
			            "<when test=\"sort != null and sort.trim() != ''\">" + 
			                "order by ${sort} ${order}" + 
			            "</when>" + 
						"<otherwise>" + 
			                "order by id desc" + 
						"</otherwise>" + 
			        "</choose>"+
					"</script>")
	List<Map<String,Object>> wxList(Map<String,Object> map);
	
	
	@Select("<script>" +
			"select c.*,ct.id subId,ct.startTime subTime,ct.coursePoint subPoint,"
			+ "ct.maxBabys subMaxBabys, ct.remainderNumber subRemainderNumber,"
			+ "ct.signedNumber subSignedNumber,ct.courseStatus subStatus,ct.courseDuration subDuration "
			+ " from tb_course c left join tb_course_time ct on c.id=ct.courseId "
			+ " where  c.courseStatus=2 and ct.courseId =(select courseId from tb_course_time t where t.id=#{id}) and ct.id != #{id}"
			+ "   and exists(select cu.id from tb_customer cu where cu.id=c.courseOwner and cu.status=2)  order by ct.id asc  " + 
					"</script>")
	List<Map<String,Object>> recommendList(Map<String,Object> map);
	
	
	@Select("<script>" +
	"select count(*) from sys_user " + 
				"<where>" + 
				  "<if test=\"id != null and id != ''\">"+ "and id = #{id} " + "</if>" + 
				  "<if test=\"placeFirst != null and placeFirst != ''\">"+ "and placeFirst = #{placeFirst} " + "</if>" + 
				  "<if test=\"placeSecond != null and placeSecond != ''\">"+ "and placeSecond = #{placeSecond} " + "</if>" + 
				  "<if test=\"ageRange != null and ageRange != ''\">"+ "and ageRange = #{ageRange} " + "</if>" + 
				  "<if test=\"coursePoints != null and coursePoints != ''\">"+ "and coursePoints = #{coursePoints} " + "</if>" + 
				  "<if test=\"courseType != null and courseType != ''\">"+ "and courseType = #{courseType} " + "</if>" + 
				  "<if test=\"courseSpec != null and courseSpec != ''\">"+ "and courseSpec = #{courseSpec} " + "</if>" + 
				  "<if test=\"courseStatus != null and courseStatus != ''\">"+ "and courseStatus = #{courseStatus} " + "</if>" + 
				"</where>"+ 
			"</script>")
	int count(Map<String,Object> map);
	
	@Insert("insert into tb_course (`courseName`, `startTime`, `endTime`, `placeFirst`, `placeSecond`,"
			+ " `ageRange`, `courseType`, `coursePoints`, `courseSpec`, `courseStatus`,"
			+ " `maxBabys`, `signedNumber`, `remainderNumber`, `courseOwner`, `courseImgs`,"
			+ " `coursePrice`, `createTime`, `createUserId`,`enterStartTime`, `enterEndTime`,"
			+ " `courseDuration`, `minAge`, `maxAge`, `arriveTime`, `isCancel`,"
			+ " `needParents`, `tasteTimes`, `tastePrice`, `memo`, `presentItems`, `marketPrice`, "
			+ "`courseBrief`"
			+ ", `ageUnit`, `isNaturalDay`, `weekDays`, `carouselImgs`, `currentStep`)"
	+ "values (#{courseName}, #{startTime}, #{endTime}, #{placeFirst}, #{placeSecond}, "
	+ "#{ageRange}, #{courseType}, #{coursePoints}, #{courseSpec}, #{courseStatus},"
	+ "#{maxBabys}, #{signedNumber}, #{remainderNumber}, #{courseOwner}, #{courseImgs},"
	+ " #{coursePrice}, #{createTime}, #{createUserId}, #{enterStartTime}, #{enterEndTime},"
	+ "	 #{courseDuration}, #{minAge}, #{maxAge}, #{arriveTime},#{isCancel}," + 
	"	 #{needParents}, #{tasteTimes}, #{tastePrice}, #{memo}, #{presentItems}, #{marketPrice}"
	+ ", #{courseBrief}, #{ageUnit}, #{isNaturalDay}, #{weekDays}, #{carouselImgs}, #{currentStep})")
	@Options(useGeneratedKeys=true)
	int save(Course c);
	
	@Update("<script>"+ 
			"update tb_course " + 
					"<set>" + 
		            "<if test=\"id != null\">`id` = #{id}, </if>" + 
                    "<if test=\"courseName != null\">`courseName` = #{courseName}, </if>" + 
                    "<if test=\"startTime != null\">`startTime` = #{startTime}, </if>" + 
                    "<if test=\"endTime != null\">`endTime` = #{endTime}, </if>" + 
                    "<if test=\"placeFirst != null\">`placeFirst` = #{placeFirst}, </if>" + 
                    "<if test=\"placeSecond != null\">`placeSecond` = #{placeSecond}, </if>" + 
                    "<if test=\"ageRange != null\">`ageRange` = #{ageRange}, </if>" + 
                    "<if test=\"courseType != null\">`courseType` = #{courseType}, </if>" + 
                    "<if test=\"coursePoints != null\">`coursePoints` = #{coursePoints}, </if>" + 
                    "<if test=\"courseSpec != null\">`gmt_create` = #{gmtCreate}, </if>" + 
                    "<if test=\"courseStatus != null\">`courseStatus` = #{courseStatus}, </if>" + 
                    "<if test=\"maxBabys != null\">`maxBabys` = #{maxBabys}, </if>" + 
                    "<if test=\"signedNumber != null\">`signedNumber` = #{signedNumber}, </if>" + 
                    "<if test=\"remainderNumber != null\">`remainderNumber` = #{remainderNumber}, </if>" + 
                    "<if test=\"courseOwner != null\">`courseOwner` = #{courseOwner}, </if>" + 
                    "<if test=\"courseImgs != null\">`courseImgs` = #{courseImgs}, </if>" + 
                    "<if test=\"coursePrice != null\">`coursePrice` = #{coursePrice}, </if>" + 
                    "<if test=\"enterStartTime != null\">`enterStartTime` = #{enterStartTime}, </if>" + 
                    "<if test=\"enterEndTime != null\">`enterEndTime` = #{enterEndTime}, </if>" + 
                    "<if test=\"updateTime != null\">`updateTime` = #{updateTime}, </if>" + 
                    "<if test=\"updateUserId != null\">`updateUserId` = #{updateUserId}, </if>" + 
                    "<if test=\"courseDuration != null\">`courseDuration` = #{courseDuration}, </if>" +
                    "<if test=\"minAge != null\">`minAge` = #{minAge}, </if>" +
                    "<if test=\"maxAge != null\">`maxAge` = #{maxAge}, </if>" +
                    "<if test=\"arriveTime != null\">`arriveTime` = #{arriveTime}, </if>" +
                    "<if test=\"isCancel != null\">`isCancel` = #{isCancel}, </if>" +
                    "<if test=\"needParents != null\">`needParents` = #{needParents}, </if>" +
                    "<if test=\"tasteTimes != null\">`tasteTimes` = #{tasteTimes}, </if>" +
                    "<if test=\"tastePrice != null\">`tastePrice` = #{tastePrice}, </if>" +
                    "<if test=\"memo != null\">`memo` = #{memo}, </if>" +
                    "<if test=\"presentItems != null\">`presentItems` = #{presentItems}, </if>" +
                    "<if test=\"marketPrice != null\">`marketPrice` = #{marketPrice}, </if>" +
                    "<if test=\"courseBrief != null\">`courseBrief` = #{courseBrief}, </if>" +
                    "<if test=\"ageUnit != null\">`ageUnit` = #{ageUnit}, </if>" +
                    "<if test=\"isNaturalDay != null\">`isNaturalDay` = #{isNaturalDay}, </if>" +
                    "<if test=\"weekDays != null\">`weekDays` = #{weekDays}, </if>" +
                    "<if test=\"carouselImgs != null\">`carouselImgs` = #{carouselImgs}, </if>" +
                    "<if test=\"currentStep != null\">`currentStep` = #{currentStep}, </if>" +
  					"`updateTime` = now(), " +
          			"</set>" + 
					"where id = #{id}"+
			"</script>")
	int update(Course sysUser);
	
	@Delete("delete from tb_course where id =#{id}")
	int remove(Long id);
	
	@Delete("<script>"+ 
	"delete from tb_course where id in "+
	"<foreach collection=\"list\" index=\"i\" open=\"(\" separator=\",\" close=\")\" item=\"item\"  >#{item}</foreach>"+
	"</script>")
	int batchRemove(List<Long> list);
	
	
	@Update("<script>"+ 
			"update tb_course " + 
					"<set>" + 
		            "<if test=\"id != null\">`id` = #{id}, </if>" + 
                    "<if test=\"courseStatus != null\">`courseStatus` = #{courseStatus}, </if>" + 
  					"`updateTime` = now(), " +
          			"</set>" + 
					"where id = #{id}"+
			"</script>")
	int updateStatus(Course c);
	
}
